CREATE FUNCTION [dbo].[asi_PublishedChildDocumentsByDocumentKey](@documentVersionKey uniqueidentifier)
RETURNS @tblArray TABLE ( [DocumentVersionKey] uniqueidentifier,
                          [Title] nvarchar(100),
                          [DocumentTypeCode] nvarchar(3),
                          [CreatedOn] datetime,
                          [CreatedByUserKey] uniqueidentifier,
                          [HierarchyKey] uniqueidentifier,
                          [Level] int
   )
AS
BEGIN
    -- Get ParentHierarchyKey
    DECLARE @parentHierarchyKey uniqueidentifier
    SELECT @parentHierarchyKey = h.[HierarchyKey]
      FROM [dbo].[DocumentMain] d
           INNER JOIN [dbo].[Hierarchy] h ON d.[DocumentVersionKey] = h.[UniformKey]
     WHERE d.[DocumentVersionKey] = @documentVersionKey
           AND d.DocumentStatusCode = 40

    ;WITH ChildDocuments ([DocumentVersionKey], [Title], [DocumentTypeCode], [CreatedOn], [CreatedByUserKey], [HierarchyKey], [Level])
    AS
    (
    -- Anchor member definition
         SELECT d.[DocumentVersionKey],
                CASE WHEN d.[AlternateName] IS NULL OR d.[AlternateName] = '' THEN d.[DocumentName] ELSE d.[AlternateName] END AS [Title],
                d.[DocumentTypeCode],
                d.[CreatedOn],
                d.[CreatedByUserKey],
                h.[HierarchyKey],
                0 AS [Level]
           FROM [dbo].[DocumentMain] d
                INNER JOIN [dbo].[Hierarchy] h ON d.[DocumentVersionKey] = h.[UniformKey]
          WHERE h.[ParentHierarchyKey] = @parentHierarchyKey
                AND d.DocumentStatusCode = 40
        UNION ALL
    -- Recursive member definition
         SELECT d.[DocumentVersionKey],
                CASE WHEN d.[AlternateName] IS NULL OR d.[AlternateName] = '' THEN d.[DocumentName] ELSE d.[AlternateName] END AS [Title],
                d.[DocumentTypeCode],
                d.[CreatedOn],
                d.[CreatedByUserKey],
                h.[HierarchyKey],
                [Level] + 1
           FROM [dbo].[DocumentMain] d
                INNER JOIN [dbo].[Hierarchy] h ON d.[DocumentVersionKey] = h.[UniformKey]
                INNER JOIN ChildDocuments AS c ON h.ParentHierarchyKey = c.HierarchyKey
          WHERE d.[DocumentStatusCode] = 40
    )
    -- Statement that executes the CTE
    INSERT INTO @tblArray
    SELECT [DocumentVersionKey], [Title], [DocumentTypeCode], [CreatedOn], [CreatedByUserKey], [HierarchyKey], [Level]
      FROM ChildDocuments
    RETURN
END



GO